package com.br.mercarad.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.table.TableColumn;

import com.br.mercarad.db.vos.CampoVO;
import com.br.mercarad.db.vos.TabelaVO;

public class DbManagerSqLite {
	Connection connection;

	// Statement statement;

	public DbManagerSqLite(String database) {
		try {

			Class.forName("org.sqlite.JDBC");
			connection = newConnection(database);
			connection.setAutoCommit(false);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static Connection newConnection(String database) {
		try {
			return DriverManager.getConnection("jdbc:sqlite:" + database);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// private Statement getStatement() {
	// try {
	// // /if (statement == null)// || statement.isClosed())
	// statement = connection.createStatement();
	// } catch (SQLException e) {
	//
	// e.printStackTrace();
	// }
	// return statement;
	// }

	private boolean execute(String sql) {
		Statement stat = null;
		try {
			stat = connection.createStatement();
			stat.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	public List<String> getAllTables() {
		Statement stat = null;
		List<String> retorno = new ArrayList<String>();

		try {
			stat = connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from sqlite_master where type = 'table'");
			while (rs.next()) {
				retorno.add(rs.getString("tbl_name"));
			}
			return retorno;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return retorno;

	}

	public TabelaVO getCamposFromTable(String tableName) {
		Statement stat = null;
		TabelaVO retorno = new TabelaVO();
		try {
			stat = connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from sqlite_master where type = 'table' and tbl_name = '" + tableName + "'");
			rs.next();
			String sql = rs.getString("sql");
			retorno = extractCamposVO(sql);
			retorno.setDdl(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return retorno;

	}

	private TabelaVO extractCamposVO(String sql) {
		TabelaVO retorno = new TabelaVO();
		retorno.setCampos(new ArrayList<CampoVO>());
		sql = sql.trim();
		sql = sql.replaceAll("(?i)\\(", " ");
		sql = sql.replaceAll("( )+", " ");		
		// bind
		sql = sql.replaceAll("(?i)CREATE TABLE", "\\$CREATETABLE\\$ ");
		sql = sql.replaceAll("(?i)NOT NULL", " \\$NOTNULL\\$ ");
		sql = sql.replaceAll("(?i)PRIMARY KEY \\(", " \\$PRIMARYKEYS\\$ ");
		sql = sql.replaceAll("(?i)PRIMARY KEY", " \\$PRIMARYKEY\\$ ");
		sql = sql.replaceAll("(?i),", " , ");
		sql = sql.replaceAll("(?i) TEXT", " \\$TEXT\\$ ");
		sql = sql.replaceAll("(?i) INTEGER", " \\$INTEGER\\$ ");
		sql = sql.replaceAll("(?i) FLOAT", " \\$FLOAT\\$ ");
		sql = sql.replaceAll("(?i) REAL", " \\$REAL\\$ ");
		sql = sql.replaceAll("(?i)\\(", "");
		sql = sql.replaceAll("(?i)\\)", "");

		sql = sql.replaceAll("( )+", " ");
		sql = sql.replaceAll("\"", "");
		sql = sql.replaceAll(",,", ",");

		String[] sqls = sql.split(" ");
		retorno.setNome(sqls[1]);
		CampoVO campo = new CampoVO();
		boolean isPKSFINAL = false;
		for (int i = 2; i < sqls.length; i++) {
			if (sqls[i].equals("$NOTNULL$"))
				campo.setIsNull(false);
			else if (sqls[i].equals("$TEXT$"))
				campo.setTypeSqlite("TEXT");
			else if (sqls[i].equals("$INTEGER$"))
				campo.setTypeSqlite("INTEGER");
			else if (sqls[i].equals("$REAL$"))
				campo.setTypeSqlite("REAL");
			else if (sqls[i].equals("$FLOAT$"))
				campo.setTypeSqlite("FLOAT");
			else if (sqls[i].equals("$PRIMARYKEYS$"))
				isPKSFINAL = true;			
			else if (sqls[i].equals("$PRIMARYKEY$"))
				campo.setIsPrimary(true);			
			else if (sqls[i].equals(",")) {				
				if (!isPKSFINAL) {
					
					retorno.getCampos().add(campo);
					campo = new CampoVO();
				}
			} else {
				// TODO pode darbugggg
				if (isPKSFINAL)
					retorno.setCampoIsPk(sqls[i]);
				else {
					campo.setNome(sqls[i]);
					retorno.setSulfixo(sqls[i].split("_")[1]);
				}
			}
		}
		if (campo.getNome()!=null)
			retorno.getCampos().add(campo);

		return retorno;
	}

	public void executaScriptCreate(String sqlFile) {
		FileReader fileReader = null;
		BufferedReader bufferedReader = null;

		try {
			fileReader = new FileReader(sqlFile);
			bufferedReader = new BufferedReader(fileReader);

			String line = bufferedReader.readLine();
			while (line != null) {
				if (line.length() > 0) {
					Statement stat = connection.createStatement();
					stat.execute(line);
				}
				line = bufferedReader.readLine();
			}

		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (bufferedReader != null)
					bufferedReader.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}

}
